-- @owner: danny20001210
-- @date: 2025-5-15
-- @testpoint:验证不同表类型使用xmltype模块正确，success，部分用例合理报错
@conn PrimaryDbAmode;
drop table if exists row_1265687 cascade;
drop table if exists col_1265687 cascade;
drop table if exists unlogged_1265687 cascade;
drop table if exists row_compress_1265687 cascade;
drop table if exists ustore_1265687 cascade;
drop table if exists astore_1265687 cascade;
drop table if exists hash_1265687 cascade;
drop table if exists range_1265687 cascade;
drop table if exists list_1265687 cascade;
drop table if exists interval_1265687 cascade;
drop table if exists sub_1265687 cascade;

create table row_1265687( ID int, name varchar2(265), data xmltype )with (ORIENTATION = row);
create table col_1265687( ID int, name varchar2(265), data xmltype )with (ORIENTATION = COLUMN);
create unlogged table unlogged_1265687( ID int, name varchar2(265), data xmltype );
create table row_compress_1265687( ID int, name varchar2(265), data xmltype )with (COMPRESSTYPE = 1);
create table ustore_1265687( ID int, name varchar2(265), data xmltype )with (storage_type=ustore);
create table astore_1265687( ID int, name varchar2(265), data xmltype )with (storage_type=astore);
create table hash_1265687( ID int, name varchar2(265), data xmltype ) PARTITION BY HASH (id)(PARTITION p1,PARTITION p2);
create table range_1265687( ID int, name varchar2(265), data xmltype )partition by range(id) (
partition p1 values less than (2),
partition p2 values less than (4),
partition p3 values less than (6),
partition p4 values less than (8),
partition p5 values less than (MAXVALUE)
) ;
create table list_1265687( ID int, name varchar2(265), data xmltype )partition by list(id) (
partition p1 values (1),
partition p2 values (2),
partition p3 values (4),
partition p4 values (6),
partition p5 values (default)
) ;
create table interval_1265687( ID int, name varchar2(265), data xmltype ,f1 date) partition by range(f1)
interval ('1 month')(
partition p1 values less than ('2000-03-01'),
partition p2 values less than ('2000-04-01'),
partition p3 values less than ('2000-05-01'),
partition p4 values less than ('2000-06-01'),
partition p5 values less than ('2000-07-01')) ;
create table sub_1265687( ID int, name varchar2(265), data xmltype )partition by range(data)
subpartition by hash(id)
(
partition p1 values less than(10)
(
subpartition p1_1,
subpartition p1_2),
partition p2 values less than(20)
(
subpartition p2_1,
subpartition p2_2));
create table sub_1265687( ID int, name varchar2(265), data xmltype )partition by range(id)
subpartition by hash(id)
(
partition p1 values less than(10)
(
subpartition p1_1,
subpartition p1_2),
partition p2 values less than(20)
(
subpartition p2_1,
subpartition p2_2));


Insert INTO row_1265687 VALUES (1,'test xml doc',xmlType.createXML('<?xml version="1.0" encoding="UTF-8" ?> <collection xmlns=""> <record> <leader>-----nam0-22-----^^^450-</leader> <datafield tag="200" ind1="1" ind2=" "> <subfield code="a">抗震救灾</subfield> <subfield code="f">奥运会</subfield> </datafield> <datafield tag="209" ind1=" " ind2=" "> <subfield code="a">经济学</subfield> <subfield code="b">计算机</subfield> <subfield code="c">10001</subfield> <subfield code="d">2005-07-09</subfield> </datafield> <datafield tag="610" ind1="0" ind3=" "> <subfield code="a">计算机</subfield> <subfield code="a">笔记本</subfield> </datafield> </record> </collection>')) ;

select extract(x.data,'/collection/record/datafield/subfield') xmlseq from row_1265687 x;
select XMLSequence(extract(x.data,'/collection/record/datafield/subfield')) xmlseq from row_1265687 x;
select extractvalue(x.data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]') as A from row_1265687 x;
select existsnode(x.data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]') as a from row_1265687 x;
select getStringVal(extract(x.data,'/collection/record/datafield/subfield')) a from row_1265687 x;


Insert INTO unlogged_1265687 VALUES (1,'test xml doc',xmlType.createXML('<?xml version="1.0" encoding="UTF-8" ?> <collection xmlns=""> <record> <leader>-----nam0-22-----^^^450-</leader> <datafield tag="200" ind1="1" ind2=" "> <subfield code="a">抗震救灾</subfield> <subfield code="f">奥运会</subfield> </datafield> <datafield tag="209" ind1=" " ind2=" "> <subfield code="a">经济学</subfield> <subfield code="b">计算机</subfield> <subfield code="c">10001</subfield> <subfield code="d">2005-07-09</subfield> </datafield> <datafield tag="610" ind1="0" ind3=" "> <subfield code="a">计算机</subfield> <subfield code="a">笔记本</subfield> </datafield> </record> </collection>')) ;

select extract(x.data,'/collection/record/datafield/subfield') xmlseq from unlogged_1265687 x;
select XMLSequence(extract(x.data,'/collection/record/datafield/subfield')) xmlseq from unlogged_1265687 x;
select extractvalue(x.data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]') as A from unlogged_1265687 x;
select existsnode(x.data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]') as a from unlogged_1265687 x;
select getStringVal(extract(x.data,'/collection/record/datafield/subfield')) a from unlogged_1265687 x;

Insert INTO ustore_1265687 VALUES (1,'test xml doc',xmlType.createXML('<?xml version="1.0" encoding="UTF-8" ?> <collection xmlns=""> <record> <leader>-----nam0-22-----^^^450-</leader> <datafield tag="200" ind1="1" ind2=" "> <subfield code="a">抗震救灾</subfield> <subfield code="f">奥运会</subfield> </datafield> <datafield tag="209" ind1=" " ind2=" "> <subfield code="a">经济学</subfield> <subfield code="b">计算机</subfield> <subfield code="c">10001</subfield> <subfield code="d">2005-07-09</subfield> </datafield> <datafield tag="610" ind1="0" ind3=" "> <subfield code="a">计算机</subfield> <subfield code="a">笔记本</subfield> </datafield> </record> </collection>')) ;

select extract(x.data,'/collection/record/datafield/subfield') xmlseq from ustore_1265687 x;
select XMLSequence(extract(x.data,'/collection/record/datafield/subfield')) xmlseq from ustore_1265687 x;
select extractvalue(x.data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]') as A from ustore_1265687 x;
select existsnode(x.data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]') as a from ustore_1265687 x;
select getStringVal(extract(x.data,'/collection/record/datafield/subfield')) a from ustore_1265687 x;

Insert INTO astore_1265687 VALUES (1,'test xml doc',xmlType.createXML('<?xml version="1.0" encoding="UTF-8" ?> <collection xmlns=""> <record> <leader>-----nam0-22-----^^^450-</leader> <datafield tag="200" ind1="1" ind2=" "> <subfield code="a">抗震救灾</subfield> <subfield code="f">奥运会</subfield> </datafield> <datafield tag="209" ind1=" " ind2=" "> <subfield code="a">经济学</subfield> <subfield code="b">计算机</subfield> <subfield code="c">10001</subfield> <subfield code="d">2005-07-09</subfield> </datafield> <datafield tag="610" ind1="0" ind3=" "> <subfield code="a">计算机</subfield> <subfield code="a">笔记本</subfield> </datafield> </record> </collection>')) ;

select extract(x.data,'/collection/record/datafield/subfield') xmlseq from astore_1265687 x;
select XMLSequence(extract(x.data,'/collection/record/datafield/subfield')) xmlseq from astore_1265687 x;
select extractvalue(x.data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]') as A from astore_1265687 x;
select existsnode(x.data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]') as a from astore_1265687 x;
select getStringVal(extract(x.data,'/collection/record/datafield/subfield')) a from astore_1265687 x;

Insert INTO hash_1265687 VALUES (1,'test xml doc',xmlType.createXML('<?xml version="1.0" encoding="UTF-8" ?> <collection xmlns=""> <record> <leader>-----nam0-22-----^^^450-</leader> <datafield tag="200" ind1="1" ind2=" "> <subfield code="a">抗震救灾</subfield> <subfield code="f">奥运会</subfield> </datafield> <datafield tag="209" ind1=" " ind2=" "> <subfield code="a">经济学</subfield> <subfield code="b">计算机</subfield> <subfield code="c">10001</subfield> <subfield code="d">2005-07-09</subfield> </datafield> <datafield tag="610" ind1="0" ind3=" "> <subfield code="a">计算机</subfield> <subfield code="a">笔记本</subfield> </datafield> </record> </collection>')) ;

select extract(x.data,'/collection/record/datafield/subfield') xmlseq from hash_1265687 x;
select XMLSequence(extract(x.data,'/collection/record/datafield/subfield')) xmlseq from hash_1265687 x;
select extractvalue(x.data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]') as A from hash_1265687 x;
select existsnode(x.data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]') as a from hash_1265687 x;
select getStringVal(extract(x.data,'/collection/record/datafield/subfield')) a from hash_1265687 x;




Insert INTO list_1265687 VALUES (1,'test xml doc',xmlType.createXML('<?xml version="1.0" encoding="UTF-8" ?> <collection xmlns=""> <record> <leader>-----nam0-22-----^^^450-</leader> <datafield tag="200" ind1="1" ind2=" "> <subfield code="a">抗震救灾</subfield> <subfield code="f">奥运会</subfield> </datafield> <datafield tag="209" ind1=" " ind2=" "> <subfield code="a">经济学</subfield> <subfield code="b">计算机</subfield> <subfield code="c">10001</subfield> <subfield code="d">2005-07-09</subfield> </datafield> <datafield tag="610" ind1="0" ind3=" "> <subfield code="a">计算机</subfield> <subfield code="a">笔记本</subfield> </datafield> </record> </collection>')) ;

select extract(x.data,'/collection/record/datafield/subfield') xmlseq from list_1265687 x;
select XMLSequence(extract(x.data,'/collection/record/datafield/subfield')) xmlseq from list_1265687 x;
select extractvalue(x.data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]') as A from list_1265687 x;
select existsnode(x.data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]') as a from list_1265687 x;
select getStringVal(extract(x.data,'/collection/record/datafield/subfield')) a from list_1265687 x;


Insert INTO interval_1265687 VALUES (1,'test xml doc',xmlType.createXML('<?xml version="1.0" encoding="UTF-8" ?> <collection xmlns=""> <record> <leader>-----nam0-22-----^^^450-</leader> <datafield tag="200" ind1="1" ind2=" "> <subfield code="a">抗震救灾</subfield> <subfield code="f">奥运会</subfield> </datafield> <datafield tag="209" ind1=" " ind2=" "> <subfield code="a">经济学</subfield> <subfield code="b">计算机</subfield> <subfield code="c">10001</subfield> <subfield code="d">2005-07-09</subfield> </datafield> <datafield tag="610" ind1="0" ind3=" "> <subfield code="a">计算机</subfield> <subfield code="a">笔记本</subfield> </datafield> </record> </collection>'),'2000-04-01') ;

select extract(x.data,'/collection/record/datafield/subfield') xmlseq from interval_1265687 x;
select XMLSequence(extract(x.data,'/collection/record/datafield/subfield')) xmlseq from interval_1265687 x;
select extractvalue(x.data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]') as A from interval_1265687 x;
select existsnode(x.data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]') as a from interval_1265687 x;
select getStringVal(extract(x.data,'/collection/record/datafield/subfield')) a from interval_1265687 x;

Insert INTO range_1265687 VALUES (1,'test xml doc',xmlType.createXML('<?xml version="1.0" encoding="UTF-8" ?> <collection xmlns=""> <record> <leader>-----nam0-22-----^^^450-</leader> <datafield tag="200" ind1="1" ind2=" "> <subfield code="a">抗震救灾</subfield> <subfield code="f">奥运会</subfield> </datafield> <datafield tag="209" ind1=" " ind2=" "> <subfield code="a">经济学</subfield> <subfield code="b">计算机</subfield> <subfield code="c">10001</subfield> <subfield code="d">2005-07-09</subfield> </datafield> <datafield tag="610" ind1="0" ind3=" "> <subfield code="a">计算机</subfield> <subfield code="a">笔记本</subfield> </datafield> </record> </collection>')) ;

select extract(x.data,'/collection/record/datafield/subfield') xmlseq from range_1265687 x;
select XMLSequence(extract(x.data,'/collection/record/datafield/subfield')) xmlseq from range_1265687 x;
select extractvalue(x.data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]') as A from range_1265687 x;
select existsnode(x.data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]') as a from range_1265687 x;
select getStringVal(extract(x.data,'/collection/record/datafield/subfield')) a from range_1265687 x;
Insert INTO sub_1265687   VALUES (10,'test xml doc',xmlType.createXML('<?xml version="1.0" encoding="UTF-8" ?> <collection xmlns=""> <record> <leader>-----nam0-22-----^^^450-</leader> <datafield tag="200" ind1="1" ind2=" "> <subfield code="a">抗震救灾</subfield> <subfield code="f">奥运会</subfield> </datafield> <datafield tag="209" ind1=" " ind2=" "> <subfield code="a">经济学</subfield> <subfield code="b">计算机</subfield> <subfield code="c">10001</subfield> <subfield code="d">2005-07-09</subfield> </datafield> <datafield tag="610" ind1="0" ind3=" "> <subfield code="a">计算机</subfield> <subfield code="a">笔记本</subfield> </datafield> </record> </collection>')) ;

select extract(x.data,'/collection/record/datafield/subfield') xmlseq from sub_1265687 x;
select XMLSequence(extract(x.data,'/collection/record/datafield/subfield')) xmlseq from sub_1265687 x;
select extractvalue(x.data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]') as A from sub_1265687 x;
select existsnode(x.data,'/collection/record/datafield[@tag="209"]/subfield[@code="a"]') as a from sub_1265687 x;
select getStringVal(extract(x.data,'/collection/record/datafield/subfield')) a from sub_1265687 x;

drop table if exists row_1265687 cascade;
drop table if exists col_1265687 cascade;
drop table if exists unlogged_1265687 cascade;
drop table if exists row_compress_1265687 cascade;
drop table if exists ustore_1265687 cascade;
drop table if exists astore_1265687 cascade;
drop table if exists hash_1265687 cascade;
drop table if exists range_1265687 cascade;
drop table if exists list_1265687 cascade;
drop table if exists interval_1265687 cascade;
drop table if exists sub_1265687 cascade;
